/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package models.DAO;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import models.DBHandler;
import models.beans.Game;

/**
 *
 * @author Hoang Dong Tien
 */
public class GameDAO {

    public static List<Game> getAllGamesByCondition(String whereClause) throws ClassNotFoundException, SQLException {
        List<Game> games = new ArrayList<Game>();
        ResultSet rs = null;
        Connection oconn = DBHandler.getConnection();
        StringBuilder sb = new StringBuilder("SELECT * FROM [Games] ");
        if(whereClause != null){
            sb.append(whereClause);
        }
        PreparedStatement pstmt = oconn.prepareStatement(sb.toString());
        rs = pstmt.executeQuery();

        Game gameTmp = null;
        while(rs.next()){
            gameTmp = new Game();
            gameTmp.setGameId(rs.getInt("GameId"));
            gameTmp.setDescription(rs.getNString("Description"));
            gameTmp.setEndDate(rs.getDate("Date"));
            gameTmp.setGameImage(rs.getNString("GameImage"));
            gameTmp.setGameName(rs.getNString("GameName"));
            gameTmp.setWinnerId(rs.getInt("WinnerId"));
            gameTmp.setStartDate(rs.getDate("StartDate"));
            games.add(gameTmp);
        }

        DBHandler.closeDBObj(rs);
        DBHandler.closeDBObj(pstmt);
        DBHandler.closeDBObj(oconn);

        return games;
    }

    public static int addNewGame(Game newgame) throws ClassNotFoundException, SQLException {
        int result = 0;

        Connection oconn = DBHandler.getConnection();
        PreparedStatement pstmt = oconn.prepareStatement("INSERT INTO [BluePumpkin].[dbo].[Games]([GameName] ,[WinnerId] ,[StartDate] ,[EndDate] ,[Description] ,[GameImage] ,[CreatedUserId]) VALUES(?,?,?,?,?,?,?)");
        pstmt.setNString    (1, newgame.getGameName());
        pstmt.setInt        (2, newgame.getWinnerId());
        pstmt.setDate       (3, newgame.getStartDate_sql());
        pstmt.setDate       (4, newgame.getEndDate_sql());
        pstmt.setNString    (5, newgame.getDescription());
        pstmt.setNString    (6, newgame.getGameImage());
        pstmt.setInt        (7, newgame.getCreateUserId());
        
        result = pstmt.executeUpdate();

       

        DBHandler.closeDBObj(pstmt);
        DBHandler.closeDBObj(oconn);
        return result;
    }

    public static int updateGame(Game newgame) throws ClassNotFoundException, SQLException {
        int result = 0;

        Connection oconn = DBHandler.getConnection();
        PreparedStatement pstmt = oconn.prepareStatement("UPDATE [BluePumpkin].[dbo].[Games] SET [GameName] = ?,[GameType] = ?,[StartDate] = ?,[EndDate] = ?,[Description] = ?,[GameImage] = ?,[CreatedUserId] = ? WHERE  [GameId]=?");
        pstmt.setNString(1, newgame.getGameName());
        pstmt.setInt    (2, newgame.getWinnerId());
        pstmt.setDate   (3, newgame.getStartDate_sql());
        pstmt.setDate   (4, newgame.getEndDate_sql());
        pstmt.setNString(5, newgame.getDescription());
        pstmt.setNString(6, newgame.getGameImage());
        pstmt.setInt    (7, newgame.getCreateUserId());
        pstmt.setInt    (8, newgame.getGameId());
        
        result = pstmt.executeUpdate();
        DBHandler.closeDBObj(pstmt);
        DBHandler.closeDBObj(oconn);
        return result;
    }
    
    public static int deleteGame(int gameId) throws ClassNotFoundException, SQLException {
        int result = 0;

        Connection oconn = DBHandler.getConnection();
        PreparedStatement pstmt = oconn.prepareStatement("DELETE FROM [Games] WHERE GameId=?");
        pstmt.setInt(1, gameId);
        result = pstmt.executeUpdate();
        DBHandler.closeDBObj(pstmt);
        DBHandler.closeDBObj(oconn);
        return result;
    }
    
    public static int deleteGame(Game game) throws ClassNotFoundException, SQLException {
        return deleteGame(game.getGameId());
    }

    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        List<Game> games = getAllGamesByCondition(null);
        for (Game game : games){
            System.out.println("Game Name : " + game.getGameName());
        }
    }
}
